package belf.migrate.plugin.db2;

import belf.migrate.api.exception.APIErrorCode;
import belf.migrate.api.exception.CatalogException;
import belf.migrate.api.table.schema.TablePath;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class DB2DDLBuilder {

    public String getSynonymDDL(Connection connection, TablePath tablePath) {
        String ddl = "";
        String sql = String.format("""
                SELECT
                	TABNAME AS SYNONYM_NAME,
                	BASE_TABSCHEMA,
                	BASE_TABNAME
                FROM SYSCAT.TABLES
                WHERE TYPE = 'A'
                	AND TABSCHEMA = '%s'
                	AND TABNAME = '%s'
                """, tablePath.getSchemaName(), tablePath.getTableName());
        try (Statement stmt = connection.createStatement()) {
            try (ResultSet rs = stmt.executeQuery(sql)) {
                if (rs.next()) {
                    String synonymName = rs.getString("SYNONYM_NAME");
                    String baseSchema = rs.getString("BASE_TABSCHEMA");
                    String baseTable = rs.getString("BASE_TABNAME");
                    ddl = String.format("CREATE OR REPLACE SYNONYM %s.%s for %s.%s",
                            tablePath.getSchemaName(),
                            synonymName,
                            baseSchema,
                            baseTable);
                }
            }
        } catch (SQLException e) {

        }
        return ddl;
    }

    /**
     * 查询单个序列的DDL
     * @param connection
     * @param tablePath
     * @return
     */
    public String getSequenceDDL(Connection connection, TablePath tablePath) {
        String ddl = "";
        String sql = String.format("""
                  SELECT SEQNAME,START,INCREMENT,MINVALUE,MAXVALUE,CYCLE,CACHE,ORDER
                  FROM SYSCAT.SEQUENCES
                  WHERE SEQSCHEMA = '%s'
                    AND SEQNAME='%s'
                """, tablePath.getSchemaName(), tablePath.getTableName());
        try (Statement stmt = connection.createStatement()){
            try (ResultSet rs = stmt.executeQuery(sql)){
                if (rs.next()) {
                    long minValue = rs.getLong("MINVALUE");
                    long maxValue = Long.MAX_VALUE;
                    try {
                        maxValue = rs.getLong("MAXVALUE");
                    } catch (SQLException e) {
                    }
                    long start = rs.getLong("START");
                    long incrementBy = rs.getLong("INCREMENT");
                    String cycleFlag = rs.getString("CYCLE");
                    long cacheSize = rs.getLong("CACHE");
                    String orderFlag = rs.getString("ORDER");
                    StringBuilder sb = new StringBuilder();
                    String template = String.format("CREATE OR REPLACE SEQUENCE %s.%s START WITH %d INCREMENT BY %d MINVALUE %d MAXVALUE %d",
                            tablePath.getSchemaName(),
                            tablePath.getTableName(),
                            start,
                            incrementBy,
                            minValue,
                            maxValue);
                    sb.append(template);
                    if ("N".equalsIgnoreCase(cycleFlag)) {
                        sb.append(" NO CYCLE");
                    }
                    if (cacheSize == 0) {
                        sb.append(" NO CACHE");
                    } else {
                        sb.append(" CACHE " + cacheSize);
                    }
                    if ("N".equalsIgnoreCase(orderFlag)) {
                        sb.append(" NO ORDER");
                    }
                    ddl = sb.toString();
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ddl;
    }

    /**
     * 一次查询指定schema下所有序列（或指定序列）的DDL
     * @param connection
     * @param schema
     * @param sequenceNames
     * @return
     * @throws CatalogException
     */
    public Map<String, String> buildSequenceDDL(Connection connection, String schema, List<String> sequenceNames) throws CatalogException {
        Map<String, String> seqDDLs = new HashMap<>();
        String sql = String.format("""
                  SELECT SEQNAME,START,INCREMENT,MINVALUE,MAXVALUE,CYCLE,CACHE,ORDER
                  FROM SYSCAT.SEQUENCES
                  WHERE SEQSCHEMA = '%s'
                """, schema);
        if (null != sequenceNames && !sequenceNames.isEmpty()) {
            List<String> quotedSeqNames = new ArrayList<>();
            for (String seqName : sequenceNames) {
                quotedSeqNames.add("'" + seqName + "'");
            }
            sql += String.format(" \n\tAND SEQNAME IN (%s)", String.join(",", quotedSeqNames));
        }

        try (Statement stmt = connection.createStatement()){
            try (ResultSet rs = stmt.executeQuery(sql)){
                while (rs.next()) {
                    String seqName = rs.getString("SEQNAME");
                    long minValue = rs.getLong("MINVALUE");
                    long maxValue = Long.MAX_VALUE;
                    try {
                        maxValue = rs.getLong("MAXVALUE");
                    } catch (SQLException e) {
                    }
                    long incrementBy = rs.getLong("INCREMENT");
                    String cycleFlag = rs.getString("CYCLE");
                    long cacheSize = rs.getLong("CACHE");
                    String orderFlag = rs.getString("ORDER");
                    StringBuilder sb = new StringBuilder();
                    String template = String.format("CREATE OR REPLACE SEQUENCE %s.%s INCREMENT BY %d MINVALUE %d MAXVALUE %d",
                            schema,
                            seqName,
                            incrementBy,
                            minValue,
                            maxValue);
                    sb.append(template);
                    if ("N".equalsIgnoreCase(cycleFlag)) {
                        sb.append(" NOCYCLE");
                    }
                    if (cacheSize == 0) {
                        sb.append(" NOCACHE");
                    }
                    if ("N".equalsIgnoreCase(orderFlag)) {
                        sb.append(" NOORDER");
                    }
                    String ddl = sb.toString();
                    seqDDLs.put(seqName, ddl);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new CatalogException(APIErrorCode.SQL_EXECUTE_ERROR, "查询DB2所有序列，请检查SQL语句：sql=" + sql, e);
        }

        return seqDDLs;
    }
}
